package com.whatever;

import java.io.File;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteCursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;

public class DatabaseHelper extends SQLiteOpenHelper {

	static final String databaseName = "database.db";
	static final String employeeTable = "Employees";
	static final String colID = "EmployeeID";
	static final String colName = "EmployeeName";
	static final String colAge = "Age";
	static final String colDept = "Dept";
	
	static final String deptTable = "Dept";
	static final String colDeptID = "DeptID";
	static final String colDeptName = "DeptName";
	
	static final String viewEmps = "ViewEmps";
	static SQLiteDatabase maindatabase;
	
	public DatabaseHelper(Context cxt) {
		super(cxt, databaseName, null, 1);
		
		SQLiteDatabase database = this.getWritableDatabase();
		ContentValues contentValues = new ContentValues();
		
		contentValues.put(colDeptID, 1);         //Set values to upload to database in form (Column Name, Value)
		contentValues.put(colDeptName, "Sales");
		database.insert(deptTable, colDeptID, contentValues); //Load from contentValues into the database
		
		contentValues.put(colDeptID, 2);
		contentValues.put(colDeptName, "IT");
		database.insert(deptTable, colDeptID, contentValues);
		database.close();
		
		File databaseFile = new File("database");
		
		this.getReadableDatabase();
		SQLiteDatabase.openOrCreateDatabase(databaseFile, (CursorFactory) new SQLiteCursor(database, null, null, null));
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		
		db.execSQL("CREATE TABLE " + deptTable + " (" + colDeptID + " INTEGER PRIMARY KEY, " + colDeptName + " TEXT)");
		
		db.execSQL("CREATE TABLE " + employeeTable + "(" + colID + " INTEGER PRIMARY KEY AUTOINCREMENT, " + 
		colName + " TEXT, " + colAge + " Integer, " + colDept + "INTEGER NOT NULL, FOREIGN KEY (" + colDept +
		") REFERENCES " + deptTable + " (" + colDeptID + "));");
		
		db.execSQL("CREATE TRIGGER fd_empdept_deptid " + " BEFORE INSERT " + " ON " + employeeTable + 
				" FOR EACH ROW BEGIN" + " SELECT CASE WHEN ((SELECT " + colDeptID + " FROM " + deptTable +
				" WHERE " + colDeptID + "=new." + colDept + " ) IS NULL)" + " THEN RAISE (ABORT,'Foreign Key Violation') END;" + 
				" END;");
		
		db.execSQL("CREATE VIEW " + viewEmps + " AS SELECT " + employeeTable + "." + colID + " AS_id," + 
				" " + employeeTable + "." + colName + "," + " " + employeeTable + "." + colAge + "," + 
				" " + deptTable + "." + colDeptName + "" + " FROM " + employeeTable + " JOIN " + deptTable + 
				" ON " + employeeTable + "." + colDept + " =" + deptTable + "." + colDeptID);
		
		//InsertDepts(db);
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		
		db.execSQL("DROP TABLE IF EXISTS " + employeeTable);
		db.execSQL("DROP TABLE IF EXISTS " + deptTable);
		
		db.execSQL("DROP TRIGGER IF EXISTS dept_id_trigger");
		db.execSQL("DROP TRIGGER IF EXISTS dept_id_trigger22");
		db.execSQL("DROP TRIGGER IF EXISTS fk_empdept_deptid");
		onCreate(db);
	}
	
	public void addEntry(int deptID, String deptName) {
		SQLiteDatabase database = this.getWritableDatabase();
		ContentValues contentValues = new ContentValues();
		
		contentValues.put(colDeptID, deptID);
		contentValues.put(colDeptName, deptName);
		database.insert(deptTable, colDeptID, contentValues);
		database.close();
	}

	public int UpdateEmp(Employee emp) {
		SQLiteDatabase db = this.getWritableDatabase();
		ContentValues contentValues = new ContentValues();
		contentValues.put(colName, emp.getName());
		contentValues.put(colAge, emp.getAge());
		contentValues.put(colDept, emp.getDept());
		return db.update(employeeTable, contentValues, colID + "=?", new String[] {String.valueOf(emp.getId())});
	}
	
	public void DeleteEmp(Employee emp) {
		SQLiteDatabase db = this.getWritableDatabase();
		db.delete(employeeTable, colID + "=?", new String[] {String.valueOf(emp.getId())});
		db.close();
	}
	
	public Cursor getAllDepts() {
		SQLiteDatabase db = this.getReadableDatabase();
		Cursor cursor = db.rawQuery("SELECT " + colDeptID + " as_id, " + colDeptName + " from " + deptTable,
				new String[] {});
		return cursor;
	}
	
	public Cursor getEmpByDept(String dept) {
		SQLiteDatabase db = this.getReadableDatabase();
		String[] columns = new String[] {"_id", colName, colAge, colDeptName}; //Columns of data to retrieve
		Cursor cursor = db.query(viewEmps, columns, colDeptName + "=?", new String[] {dept}, null, null, null);
		return cursor;
	}
	
	public int GetDeptID(String dept) {
		SQLiteDatabase db = this.getReadableDatabase();
		Cursor cursor = db.query(deptTable, new String[] {colDeptID + " as_id", colDeptName}, colDeptName + "=?", new String[] {dept}, null, null, null);
		cursor.moveToFirst();
		return cursor.getInt(cursor.getColumnIndex("_id"));
	}
}
